﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;

using permit.App_Code.Entity;

namespace permit.App_Code.DataAccess
{
    public class DepartmentAccess
    {

        string strConn = ConfigurationManager.ConnectionStrings["permitConnectionString"].ConnectionString;
        SqlConnection conn;

        public SqlConnection GetConnetcion(string connString)
        {
            conn = new SqlConnection(connString);
            if (conn.State == ConnectionState.Closed)
            {

                conn.Open();
            }
            return conn;
        }

        #region
        /// <summary>
        /// 从数据库中查询数据的,返回单条记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DepartmentTable GetDepartment(int depID)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "select * from DepartmentTable where dep_ID = @dep_ID";
            command.Parameters.Add("@dep_ID", SqlDbType.Int, 4, "dep_ID").Value = depID;
            command.Connection = GetConnetcion(strConn);


            SqlDataReader reader = command.ExecuteReader();
            int count = 0;
            DepartmentTable dep = new DepartmentTable();

            while (reader.Read())
            {

                dep.DEP_ID = depID;
                dep.DEP_NAME = reader["dep_Name"].ToString();
                dep.DEP_DESCRIPTION = reader["dep_Description"].ToString();
                dep.DEP_PRINT = Convert.ToInt32(reader["dep_Print"]);
                dep.DEP_AREACODE = reader["dep_AreaCode"].ToString();

                count++;
            }

            reader.Dispose();
            connClose();//关闭连接
            if (count > 0)
                return dep;//返回结果
            else
                return null;//没有查询到数据时返回null

        }

        #endregion

        #region


        public int UpdateDepartment(DepartmentTable dep)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE DepartmentTable SET dep_Name = @dep_Name, dep_Description = @dep_Description, dep_Print = @dep_Print, dep_AreaCode = @dep_AreaCode WHERE (dep_ID = @dep_ID)";

            command.Connection = GetConnetcion(strConn);

            command.Parameters.Add("@dep_Name", SqlDbType.VarChar, 50, "dep_Name").Value = dep.DEP_NAME;
            command.Parameters.Add("@dep_Description", SqlDbType.VarChar, 50, "dep_Description").Value = dep.DEP_DESCRIPTION;
            command.Parameters.Add("@dep_Print", SqlDbType.Int, 4, "dep_Print").Value = dep.DEP_PRINT;
            command.Parameters.Add("@dep_AreaCode", SqlDbType.VarChar, 50, "dep_AreaCode").Value = dep.DEP_AREACODE;

            command.Parameters.Add("@dep_ID", SqlDbType.Int, 4, "per_ID").Value = dep.DEP_ID;

            int count;
            count = command.ExecuteNonQuery();


            connClose();//关闭连接

            return count;//返回结果


        }
        #endregion

        /// <summary>
        /// 插入数据，影响返回行数
        /// </summary>
        /// <param name="permit"></param>
        /// <returns></returns>
        public int InsertDepartment(DepartmentTable dep)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "INSERT INTO DepartmentTable(dep_Name,dep_Description,dep_Print,dep_AreaCode)VALUES (@dep_Name,@dep_Description,@dep_Print,@dep_AreaCode)";

            command.Connection = GetConnetcion(strConn);

            command.Parameters.Add("@dep_Name", SqlDbType.VarChar, 50, "dep_Name").Value = dep.DEP_NAME;

            if (dep.DEP_DESCRIPTION == "")
            {
                command.Parameters.Add("@dep_Description", SqlDbType.VarChar, 50, "dep_Description").Value = DBNull.Value;
            }
            else
            {
                command.Parameters.Add("@dep_Description", SqlDbType.VarChar, 50, "dep_Description").Value = dep.DEP_DESCRIPTION;

            }

            command.Parameters.Add("@dep_Print", SqlDbType.Int, 4, "dep_Print").Value = dep.DEP_PRINT;
            command.Parameters.Add("@dep_AreaCode", SqlDbType.VarChar, 50, "dep_AreaCode").Value = dep.DEP_AREACODE;

            try
            {
                int count = command.ExecuteNonQuery();
                connClose();//关闭连接
                return count;
            }
            catch (Exception ex)
            {
                connClose();//关闭连接
                return 0;
            }


        }

        public int GetDepartmentPrint(string userName)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT DepartmentTable.dep_Print FROM DepartmentTable INNER JOIN UserTable ON DepartmentTable.dep_ID = UserTable.dep_ID WHERE (UserTable.user_Name = @user_Name)";
            command.Connection = GetConnetcion(strConn);
            command.Parameters.Add("@user_Name", SqlDbType.VarChar, 50, "user_Name").Value = userName;
            int dpPrint = 1;
            try
            {
                 SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    dpPrint = Convert.ToInt32(reader["dep_Print"]);
                }
                reader.Dispose();
                connClose();//关闭连接
                return dpPrint;
            }
            catch (Exception ex)
            {
                connClose();//关闭连接
                return -1;
            }
        }
        /// <summary>
        /// 删除数据，影响返回行数
        /// </summary>
        /// <param name="permit"></param>
        /// <returns></returns>
        public int DeleteDepartment(int depID)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "DELETE FROM DepartmentTable WHERE (dep_ID = @dep_ID)";

            command.Connection = GetConnetcion(strConn);

            command.Parameters.Add("@dep_ID", SqlDbType.Int, 4, "dep_ID").Value = depID;


            try
            {
                int count = command.ExecuteNonQuery();
                connClose();//关闭连接
                return count;
            }
            catch (Exception ex)
            {
                connClose();//关闭连接
                return 0;
            }


        }

        #region
        /// <summary>
        /// 从数据库中查询数据的,返回结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDepartments()
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "select * from DepartmentTable";
            command.Connection = GetConnetcion(strConn);

            SqlDataAdapter ad = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            try
            {

                ad.Fill(ds);
            }
            catch (Exception ex)
            {
                return null;
            }

            return ds;
        }

        #endregion
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// 
        public void connClose()
        {
            if (conn.State == ConnectionState.Open)
            {//判断数据库的连接状态，如果状态是打开的话就将它关闭

                conn.Close();
            }
        }

        public bool IsDepartment(string depName, string depAreaCode)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "select * from DepartmentTable where dep_Name = @dep_Name OR dep_AreaCode = @dep_AreaCode";
            command.Parameters.Add("@dep_Name",SqlDbType.VarChar, 50, "dep_Name").Value = depName;
            command.Parameters.Add("@dep_AreaCode", SqlDbType.VarChar, 50, "dep_AreaCode").Value = depAreaCode;
            command.Connection = GetConnetcion(strConn);


            SqlDataReader reader = command.ExecuteReader();
            int count = 0;   
            while (reader.Read())
            {           
                count++;
                break;
            }

            reader.Dispose();
            connClose();//关闭连接
            if (count > 0)
                return true;//返回结果
            else
                return false;//没有查询到数据时返回null
        }
    }
}
